<?php
class Shop_Model_ListForCity extends Shop_Model_Base{

	/**
	 * 表示用変換
	 * @param $tenpoList
	 * @param $tenpoimageurl
	 * @param $linkurl
	 * @param $params
	 *
	 * @return 変換後リスト
	 */
	public function convertList($tenpoList, $tenpoimageurl, $linkurl, $params)
	{
		$dispData = array();

		foreach ($tenpoList as $data) {

			// 店舗名変換
			$data['M05_TENPO_NAME'] = str_replace ('アパマンショップ', '', $data['M05_TENPO_NAME']);

			//【START】
			//2011-06-21_#0363_【バグ】店舗住所にて機種依存文字が文字化けする
			//内容：店舗名、住所、番地、備考をユニコードに変換
			//担当：SS榊原

			$data['M05_TENPO_NAME'] = Apm_Util::replaceUnicode($data['M05_TENPO_NAME']);
			$data['M05_JYUUSHO_TEXT'] = Apm_Util::replaceUnicode($data['M05_JYUUSHO_TEXT']);
			$data['M05_TENPO_BANCHI'] = Apm_Util::replaceUnicode($data['M05_TENPO_BANCHI']);
			$data['M05_TENPO_JYUUSHO_BIKOU'] = Apm_Util::replaceUnicode($data['M05_TENPO_JYUUSHO_BIKOU']);

			//【START】
			//2011-06-21_#0363_【バグ】店舗住所にて機種依存文字が文字化けする
			//内容：店舗名、住所、番地、備考をユニコードに変換

			// 無料電話番号
			$callData = $this->convertFreeCalling($data);
			$data['IS_FREE_CALL']  = $callData['IS_FREE_CALL'];
			$data['TEL_01']		= $callData['TEL_01'];
			$data['TEL_02']		= $callData['TEL_02'];
			$data['hasFreeTel']	= $callData['hasFreeTel'];
			$data['CONNECTION_NO'] = $callData['CONNECTION_NO'];
			$data['M05_TENPO_LINE_ID'] = Apm_ShopUtil::getTenpoLineId($data['M05_TENPO_CD']);
			$data['LINE_URL'] = Apm_Config_Shop::URL_LINE;
			
			//【START】
			//2011-06-21_#0363_【バグ】店舗住所にて機種依存文字が文字化けする
			//内容：営業時間をユニコードに変換
			//担当：SS榊原

			// 営業時間変換
			$data['EIGYOU_ZIKAN'] = Apm_Util::replaceUnicode($this->convertEigyouZikan($data));

			//【END】
			//2011-06-21_#0363_【バグ】店舗住所にて機種依存文字が文字化けする
			//内容：営業時間をユニコードに変換

			// 定休日変換
			$data['M05_TEIKYUUBI_NAME'] = $this->convertTeikyuubi($data);

			// 郵便番号変換
			$data['M05_TENPO_YUUBIN'] = substr($data['M05_TENPO_YUUBIN'], 0, 3) . '-' . substr($data['M05_TENPO_YUUBIN'], 3, 4);

			//高津情報変換
			$data['KOUTSUU_STR'] = Apm_ShopUtil::GetTrafficStr($data);
			
			// 店舗外観写真
			$data['IMAGE_PATH'] = $tenpoimageurl . 'g' . Apm_ShopUtil::getOldTenpoCd($data['M05_TENPO_CD']) . '.jpg';
			// スタッフ写真
			$data['STAFF_IMAGE_PATH'] = $tenpoimageurl . 's' . Apm_ShopUtil::getOldTenpoCd($data['M05_TENPO_CD']) . '.jpg';

			// リンク先URL
			$data['SHOP_DTL_URL'] = Apm_Bukkenurl::generateUrlByParametersShopDetail($data);
			if (!empty($params['OemCd'])) $data['SHOP_DTL_URL'] .= '?OemCd=' . $params['OemCd'];
			//$data['SHOP_DTL_URL'] = $linkurl . '?ShopCd=' . $data['M05_TENPO_CD'] . '&' . substr($params, 1);

			// AKBｷｬﾝﾍﾟｰﾝ:加盟店ﾒﾝﾊﾞｰｻｲﾄ追加対応
			$data['SHOP_PAGE_URL'] = Apm_Bukkenurl::generateUrlByParametersShopDetail(array_merge($data, array('Category'=>'9')));
			//$data['SHOP_PAGE_URL'] = str_replace("shopdtl","shoppage",$linkurl) . '?ShopCd=' . $data['M05_TENPO_CD'] . '&' . substr($params, 1);

			$dispData[] = $data;
		}

		return $dispData;
	}

	/**
	 * 表示用変換(API用)
	 * @param $tenpoList
	 *
	 * @return 変換後リスト
	 */
	public function convertMobileList($tenpoList)
	{
		$dispData = array();
		
		foreach ($tenpoList as $data) {
			
			// 店舗名変換
			$data['M05_TENPO_NAME'] = str_replace('アパマンショップ', '', $data['M05_TENPO_NAME']);
			
			//店舗名、法人名、住所、番地、備考をユニコードに変換
			$data['M05_TENPO_NAME'] = Apm_Util::replaceUnicode($data['M05_TENPO_NAME']);
			$data['M05_HOUZINMEI'] = Apm_Util::replaceUnicode($data['M05_HOUZINMEI']);
			$data['M05_JYUUSHO_TEXT'] = Apm_Util::replaceUnicode($data['M05_JYUUSHO_TEXT']);
			$data['M05_TENPO_BANCHI'] = Apm_Util::replaceUnicode($data['M05_TENPO_BANCHI']);
			$data['M05_TENPO_JYUUSHO_BIKOU'] = Apm_Util::replaceUnicode($data['M05_TENPO_JYUUSHO_BIKOU']);
			
			// 郵便番号変換
			$data['CONVERT_TENPO_YUUBIN'] = substr($data['M05_TENPO_YUUBIN'], 0, 3) . '-' . substr($data['M05_TENPO_YUUBIN'], 3, 4);
			
			// 定休日変換
			$data['CONVERT_TEIKYUUBI'] = $this->convertTeikyuubi($data);
			
			// 営業時間変換
			$data['CONVERT_EIGYOU_ZIKAN'] = Apm_Util::replaceUnicode($this->convertEigyouZikan($data));
			
			// 無料電話接続番号
			$tempData = Apm_ShopUtil::getCallingData($data);
			for ($call_no=1; $call_no<=Apm_Config_Shop::CALLNOTE_NO_MAX; $call_no++) {
				$controlFlag = $data['M93_CONTROL_FLAG'.$call_no];
				if (!($tempData['IS_FREE_CALL'] && $controlFlag == '0')) {
					// 未使用であれば、接続番号を空にする
					$data['M93_CONNECTION_NO'.$call_no] = "";
				}
			}
			// 無料電話番号
			if ($tempData['IS_FREE_CALL']) {
				$shopDtl['CONVERT_FREE_CALLING_NO'] = $tempData['TEL_01'];
			}
			
			$dispData[] = $data;
		}
		
		return $dispData;
	}

	/**
	 * 店舗一覧所得sql生成（エリア検索）
	 *
	 * @param $params
	 * 		地図から検索から遷移：都道府県CD（必須）
	 * 		サービス内容から検索から遷移：都道府県CD（任意）、サービス内容（任意）
	 * @param $db_params
	 * @return sql文
	 */
	protected function generateSql($params, &$db_params)
	{
		// クエリ文字列
		$strQryCity = '';
		$strQryWhere = '';
		$strQryFreecalling = '';
		
		// 検索区分
		$strSrhKbn = $params['SrhKbn'];
		
		if (count($params['CityCd']) > 0)
		{
			// 条件生成
			$queryCityList = array();
			$i = 0;
			foreach ($params['CityCd'] as $PrefCityCd)
			{
				$i++;
				$PrefCd = substr($PrefCityCd, 0, 2);
				$CityCd = substr($PrefCityCd, 2);
				$queryCityList[] = "( M05_TENPO_TODOUFUKEN_CD = :PrefCd_{$i} AND M05_TENPO_SHIKUCHOUSON_CD = :CityCd_{$i} )";
				$db_params["PrefCd_".$i] = $PrefCd;
				$db_params["CityCd_".$i] = $CityCd;
			}
			// SQL
			$strQryCity .= " AND ( ";
			$strQryCity .= join(" OR ", $queryCityList);
			$strQryCity .= " ) ";
		}
		// マイレージトップから遷移してきた際の対応
		elseif (!empty($params['PrefCd']))
		{
			$strQryCity .= ' AND M05_TENPO_TODOUFUKEN_CD ' . $this->generateWhereIn("prefCdM05", $params['PrefCd'], $db_params);
		}
		
		// フリーワード検索
		if ($strSrhKbn == Apm_Config_Shop::SEARCH_KUBUN_FREEWORD)
		{
			$strQryWhere .= $this->generateCondFreeWord($params);
		}
		// 法人コード検索
		elseif ($strSrhKbn == Apm_Config_Shop::SEARCH_KUBUN_HOUZIN )
		{
			$strQryWhere .= " AND M05_HOUZIN_CD = :OemCd ";
			$db_params['OemCd'] = $params['OemCd'];
			$strQryWhere .= $this->generateCondService($params);
		}
		else
		{
			// サービス内容から検索
			$strQryWhere .= $this->generateCondService($params);
		}
		
		// ダミー店舗除外
		$strQryWhere .= $this->generateNotinDummyShop("M05TENPO_INFO.M05_TENPO_CD", $db_params);
		
		// 無料電話番号
		for ($call_no=1; $call_no<=Apm_Config_Shop::CALLNOTE_NO_MAX; $call_no++) {
			$strQryFreecalling .= " ,M93_CONNECTION_NO{$call_no} ";
			$strQryFreecalling .= " ,M93_CONTROL_FLAG{$call_no} ";
		}

$sql = <<< DOC_END

SELECT
	*
FROM
	(
	SELECT
		 M05TENPO_INFO.M05_TENPO_CD
		,M05_HOUZIN_CD
		,M05_TENPO_NAME
		,M05_HOUZINMEI
		,M05_JYUUSHO_TEXT
		,M05_TENPO_BANCHI
		,M05_TENPO_JYUUSHO_BIKOU
		,M05_INFORMATION
		,M05_KAITENBI
		,M05_TENPO_TODOUFUKEN_CD
		,M05_TENPO_SHIKUCHOUSON_CD
		,M05_TENPO_CHOUMEI_CD
		,M05_TENPO_TODOUFUKEN
		,M05_TENPO_SHIKUCHOUSON
		,M05_EIGYOU_ZIKAN_CLOSE_H
		,M05_EIGYOU_ZIKAN_CLOSE_M
		,M05_MIDDLE_FLAG
		,M05_TENPO_TEL01
		,M05_TENPO_TEL02
		,CASE
			WHEN LPAD(NVL(M05_EIGYOU_ZIKAN_CLOSE_H, ''), 2, '0') || LPAD(NVL(M05_EIGYOU_ZIKAN_CLOSE_M, ''), 2, '0') > '1900' THEN 1
			ELSE 0
		 END AS M05_EIGYOU_ZIKAN_CLOSE
		,M05_OEM
		,SUBSTR(M05TENPO_SERVICE.M05_SERVICE_FLAG, 12, 1) AS INFOMATION_CENTER_FLAG
		,M05_TENPO_YUUBIN
		,M05_KOUTSUU_TEXT01
		,M05_KOUTSUU_BIKOU01
		,M05_TOHO01
		,M05_TEIKYUUBI
		,M05_EIGYOU_ZIKAN_OPEN_H
		,M05_EIGYOU_ZIKAN_OPEN_M
		,M05_EIGYOU_ZIKAN_BIKOU
		,M05_EKI_CD01
		,M03_EKI_NAME
		,M05_TOKUCHO01
		,M05_TOKUCHO02
		,M05_TOKUCHO03
		,M05_TOKUCHO04
		,M05_TOKUCHO05
		,M05_TOKUCHO06
		,M05_TOKUCHO07
		,M05_TOKUCHO08
		,M05_TOKUCHO09
		,M05_TOKUCHO10
		,M05_TOKUCHO11
		,M05_TOKUCHO12
		,M05_TOKUCHO13
		,M05_TOKUCHO14
		,M05_TOKUCHO15
		,M05_TOKUCHO16
		,M05_ENSEN_CD01
		,M03_ENSEN_NAME
		,M02_ENSEN_KUBUN
		{$strQryFreecalling}
		,M93_USE_KBN
		,M93_START_DATE
		,M93_DEL_DATE
		,CEIL(DBMS_RANDOM.VALUE*1000000) RANDOM
	FROM
		 M05TENPO_INFO
		,M05TENPO_SERVICE
		,M03EKI
		,M02ENSEN
		,M93CALLNOTE M93
	WHERE
		M05_KOUKAI_FLAG = 1
	AND (M05TENPO_INFO.M05_TENPO_CD > '01000000' AND M05TENPO_INFO.M05_TENPO_CD < '48000000')
	AND M05TENPO_INFO.M05_TENPO_CD = M05TENPO_SERVICE.M05_TENPO_CD
	AND M05TENPO_INFO.M05_EKI_CD01 = M03EKI.M03_EKI_CD(+)
	AND M03EKI.M03_ENSEN_CD = M02ENSEN.M02_ENSEN_CD(+)
	AND M05TENPO_INFO.M05_TENPO_CD = M93.M93_TENPO_CD(+)
	{$strQryCity}
	{$strQryWhere}
	)
ORDER BY
	 M05_TENPO_TODOUFUKEN_CD ASC
	,M05_TENPO_SHIKUCHOUSON_CD ASC
	,RANDOM

DOC_END;

		return $sql;
	}
}
?>
